﻿// Copyright (c) 2013 Bryan C. O'Connell
// http://wsubi.codeplex.com/license
// Purpose: Executes a Query object against a database and collects results.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace Wsubi
{
    class QueryProcessor : IDisposable
    {
        public QueryProcessor() { }       

        /// <summary>Runs the specified query against the specified database and saves the results.</summary>
        /// <param name="UserQuery">The query to be processed.</param>
        public void Process(Query UserQuery)
        {
            _CurrentQuery = UserQuery;
            ValidateQuery();

            ConnectToDatabase();

            _Results = new List<string>();
            RunQueryAndSaveResults();
        }

        /// <summary>Checks the currently stored query to make sure it's valid.</summary>
        private void ValidateQuery()
        {
            if ((_CurrentQuery.QueryType == QueryTypes.Invalid) || (_CurrentQuery.QueryOutput == OutputsTo.Invalid))
            {
                throw new ArgumentException("Unable to process - Query Type or Output Type of specified Query is Invalid.");
            }
        }

        /// <summary>Establishes a connection to the specified database.</summary>
        private void ConnectToDatabase()
        {
            try
            {
                _SqlConnection = new SqlConnection(_CurrentQuery.ConnectionString);
                _SqlConnection.Open();

                _SqlCommand = new SqlCommand(_CurrentQuery.QueryText, _SqlConnection);
            }
            catch(Exception ex)
            {
                throw new Exception("Unable to connect to database - " + ex.Message);
            }
        }

        /// <summary>Runs the current query and stores the results as an object property for access.</summary>
        /// <remarks>To get the returned data, access the 'Results' list.</remarks>
        private void RunQueryAndSaveResults()
        {
            try
            {
                if (_CurrentQuery.QueryType == QueryTypes.NonQuery)
                {
                    int RowsAffected = _SqlCommand.ExecuteNonQuery();
                    _Results.Add(RowsAffected.ToString() + " rows affected.");
                }

                if (_CurrentQuery.QueryType == QueryTypes.Value)
                {
                    var Value = _SqlCommand.ExecuteScalar();
                    _Results.Add(Value.ToString());
                }

                if ((_CurrentQuery.QueryType == QueryTypes.Row) ||
                    (_CurrentQuery.QueryType == QueryTypes.Rowset))
                {
                    SqlDataReader Reader = _SqlCommand.ExecuteReader();
                    DataTable DataResults = new DataTable();

                    DataResults.Load(Reader);
                    PrepResultsForOutput(DataResults);
                }      
            }
            catch(Exception ex)
            {
                throw new Exception("There was a problem executing your query - " + ex.Message);
            }            
        }

        /// <summary>Converts a data table into a List of strings.</summary>
        /// <param name="QueryData">The data table to convert.</param>
        private void PrepResultsForOutput(DataTable QueryData)
        {
            if ( (QueryData != null) && (QueryData.Rows.Count > 0) )
            {
                const char PIPE = '|';

                String ColumnHeader = string.Empty;
                foreach (DataColumn column in QueryData.Columns)
                {
                    ColumnHeader += column.ColumnName + PIPE;
                }
                _Results.Add(ColumnHeader.TrimEnd(PIPE));

                foreach (DataRow Row in QueryData.Rows)
                {
                    String Record = string.Empty;
                    foreach (DataColumn column in QueryData.Columns)
                    {
                        Record += Row[column.ColumnName].ToString() + PIPE;
                    }
                    _Results.Add(Record.TrimEnd(PIPE));
                }
            }
            else
            {
                _Results.Add("Data table is empty; query did not return any results.");
            }
        }

        ~QueryProcessor() 
        {
            Dispose();
        }

        public void Dispose()
        {
            _SqlCommand.Dispose();
            _SqlConnection.Close();
            _SqlConnection.Dispose();
        }

        #region Properties

        private Query _CurrentQuery;

        private SqlConnection _SqlConnection;

        private SqlCommand _SqlCommand;

        // Results from the last query executed.
        private List<string> _Results;
        public List<string> Results
        { get { return _Results; } }

        #endregion
    }
}
